package js.web.center.database;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * 数据库工具
 * @author dowell
 *
 */
public class JsWebMySql {
	private static JsWebMySql databaseUtils;
	private ComboPooledDataSource dataSource; 
	/**
	 * 初始化连接池
	 * @param driverClass
	 * @param jdbcUrl
	 * @param username
	 * @param password
	 * @param poolsize
	 * @param minpool
	 * @param maxpool
	 * @param maxstate
	 * @param idleTime
	 */
	public final void init(String driverClass, String jdbcUrl,String username, String password,int poolsize,int minpool,int maxpool,int maxstate,int idleTime)
	{
		try {
			dataSource = new ComboPooledDataSource();
			dataSource.setDriverClass(driverClass);
			dataSource.setJdbcUrl(jdbcUrl);
			dataSource.setUser(username);
			dataSource.setPassword(password);
			dataSource.setAutoCommitOnClose(true);
			dataSource.setInitialPoolSize(poolsize);
			dataSource.setMinPoolSize(minpool);
			dataSource.setMaxPoolSize(maxpool);
			dataSource.setMaxStatements(maxstate);
			dataSource.setMaxIdleTime(idleTime);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	/**
	 * 获取数源信息
	 * @return
	 */
	public final ComboPooledDataSource getComboPool(){
		return dataSource;
	}
	public final static JsWebMySql getInstance() {
		if (databaseUtils == null)
		{
			databaseUtils = new JsWebMySql();
		}
		return databaseUtils;
	}
	/**
	 * 设置sql过滤参数
	 * @param array 参数数组
	 * @param prepareStatement 
	 * @throws SQLException
	 * @throws ParseException 
	 */
	private static void setParams(JSONArray array,PreparedStatement prepareStatement) throws SQLException, ParseException{
		for (int i = 0; i < array.size(); i++) {
			JSONObject jsonObject = array.getJSONObject(i);
			int type=jsonObject.getIntValue("type");
			int paramIdx=i+1;
			try{
			switch(type){
			case 1:
				prepareStatement.setString(paramIdx, jsonObject.getString("value"));
				break;
			case 2:
				prepareStatement.setInt(paramIdx, jsonObject.getIntValue("value"));
				break;
			case 3:
				prepareStatement.setLong(paramIdx, jsonObject.getLongValue("value"));
				break;
			case 4:
				prepareStatement.setFloat(paramIdx, jsonObject.getFloatValue("value"));
				break;
			case 5:
				prepareStatement.setDate(paramIdx, new Date(jsonObject.getDate("value").getTime()));
				break;
			case 6:
				prepareStatement.setDouble(paramIdx, jsonObject.getDoubleValue("value"));
				break;
			case 7:
				prepareStatement.setBigDecimal(paramIdx, jsonObject.getBigDecimal("value"));
				break;
			case 8:
				prepareStatement.setBoolean(paramIdx, jsonObject.getBooleanValue("value"));
				break;
			case 9:
				prepareStatement.setShort(paramIdx, jsonObject.getShortValue("value"));
				break;
			case 10:
				SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Timestamp timestamp=new Timestamp(dateFormat.parse(jsonObject.getString("value")).getTime());
				prepareStatement.setTimestamp(paramIdx, timestamp);
				break;
			}
			}catch(Exception e){
				System.out.println("Error type:"+type+" idx:"+i);
				throw e;
			}
		}
	}
	/**
	 * sql批量执行
	 * @param sqls 执行的查询语句数组
	 * @return
	 * @throws Exception 
	 */
	public static boolean queryList(String sqlsStr) throws Exception{
		JSONArray sqls=JSONArray.parseArray(sqlsStr);
		boolean backResult=true;
		Connection connection = null;
		try{
			connection = getInstance().getConnection();
			Statement createStatement = connection.createStatement();
			for (int i = 0; i < sqls.size(); i++) {
				createStatement.addBatch(sqls.getString(i));
			}
			createStatement.executeBatch();
			createStatement.close();
		}catch(Exception e){
			backResult=false;
			e.printStackTrace();
			throw e;
		}finally{
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
		}
		return backResult;
	}
	/**
	 * 插入sql返回id
	 * @param sql 插入sql
	 * @param jsonAryStr 传递参数
	 * @return
	 * @throws Exception 
	 */
	public static int queryBackInsert(String sql,String jsonAryStr,boolean back) throws Exception{
		Connection connection = null;
		int id=-1;
		try{
			connection = getInstance().getConnection();
			PreparedStatement prepareStatement = connection.prepareStatement(sql);
			if(jsonAryStr!=null&&jsonAryStr.length()>0){
				JSONArray array=JSONArray.parseArray(jsonAryStr);
				setParams(array,prepareStatement);
			}
			prepareStatement.execute();
			if(back){
				ResultSet executeQuery = prepareStatement.executeQuery("SELECT LAST_INSERT_ID() as lastid");
				while (executeQuery.next()) {
					id = executeQuery.getInt("lastid");
				}
				executeQuery.close();
			}
			prepareStatement.close();
		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
		}
		return id;
	}
	/**
	 * 查询sql返回对象
	 * @param sql 查询语句
	 * @param jsonAryStr 传送参数
	 * @param back 是否返回对象
	 * @return
	 * @throws Exception 
	 */
	public static String queryBackObject(String sql,String jsonAryStr,boolean back) throws Exception{
		Connection connection = null;
		JSONObject backJsonObj=new JSONObject();
		try{
			connection = getInstance().getConnection();
			PreparedStatement prepareStatement = connection.prepareStatement(sql);
			if(jsonAryStr!=null&&jsonAryStr.length()>0){
				JSONArray array=JSONArray.parseArray(jsonAryStr);
				setParams(array,prepareStatement);
			}
			if(back){
				ResultSet executeQuery = prepareStatement.executeQuery();
				ResultSetMetaData metaData = executeQuery.getMetaData();
				while(executeQuery.next()){
					for (int i = 0; i < metaData.getColumnCount(); i++) {
						int tempIdx=i+1;
						backJsonObj.put(metaData.getColumnLabel(tempIdx), executeQuery.getObject(tempIdx));
					}
				}
				executeQuery.close();
			}else{
				prepareStatement.execute();
			}
			prepareStatement.close();
		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					throw e;
				}
			}
		}
		return backJsonObj.toString();
	}
	/**
	 * 查询sql返回列表
	 * @param sql sql语句
	 * @param jsonAryStr 传递参数
	 * @param back 是否有返回值
	 * @return
	 * @throws Exception 
	 */
	public static String queryBackList(String sql,String jsonAryStr,boolean back) throws Exception{
		Connection connection = null;
		JSONArray backJsonAry=new JSONArray();
		try{
			connection = getInstance().getConnection();
			PreparedStatement prepareStatement = connection.prepareStatement(sql);
			if(jsonAryStr!=null&&jsonAryStr.length()>0){
				JSONArray array=JSONArray.parseArray(jsonAryStr);
				setParams(array,prepareStatement);
			}
			if(back){
				ResultSet executeQuery = prepareStatement.executeQuery();
				ResultSetMetaData metaData = executeQuery.getMetaData();
				while(executeQuery.next()){
					JSONObject object=new JSONObject();
					for (int i = 0; i < metaData.getColumnCount(); i++) {
						int tempIdx=i+1;
						object.put(metaData.getColumnLabel(tempIdx), executeQuery.getObject(tempIdx));
					}
					backJsonAry.add(object);
				}
				executeQuery.close();
			}else{
				prepareStatement.execute();
			}
			prepareStatement.close();
		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					throw e;
				}
			}
		}
		return backJsonAry.toString();
	}
	public final Connection getConnection() {
		try {		
			dataSource.setUnreturnedConnectionTimeout(30000);
			return dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("无法从数据源获取连接 ", e);
		}
	}
}
